﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class ViewFollowUpDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public ViewFollowUpDAO()
        {
            conn = ConnectionUtils.getConnection();
        }

        public IList<ViewFollowUp> findAll()
        {
            IList<ViewFollowUp> viewFollow = new List<ViewFollowUp>();
            try
            {
                conn.Open();
                String sqlCommand = " select * from vwFollowUp";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ViewFollowUp vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                    vf.DataStatus = reader.GetDateTime(18);
                    viewFollow.Add(vf);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return viewFollow;

        }

        public IList<ViewFollowUp> findByFuncionario(string idfuncionario, bool gestor, int status)
        {
            IList<ViewFollowUp> viewFollow = new List<ViewFollowUp>();
            try
            {
                conn.Open();
                String sqlCommand;

                if (status < 1)
                {
                    sqlCommand = @" select * from vwFollowUp where ";
                }

                else

                {
                    sqlCommand = @" select * from vwFollowUp where statusfollow_idstatus =  " + status + " and ";
                }
                if (!gestor)
                {
                    sqlCommand += @"funcionario_idfuncionario =" + idfuncionario.ToString();
                }
                else
                {
                    sqlCommand += @"funcionario_idfuncionario in (SELECT funcionario_idfuncionario
                                                                        FROM [equipefuncionario]
                                                                        where equipe_idequipe = (SELECT equipe_idequipe
                                                                        FROM [equipefuncionario]
                                                                        where funcionario_idfuncionario = " + idfuncionario.ToString() + @"))";
                }

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ViewFollowUp vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                    vf.DataStatus = reader.GetDateTime(18);
                    viewFollow.Add(vf);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return viewFollow;

        }


        public IList<ViewFollowUp> findByContato(Int32 idcliente, int status)
        {
            IList<ViewFollowUp> viewFollow = new List<ViewFollowUp>();
            try
            {
                conn.Open();
                String sqlCommand;
                if (status < 1)
                {
                    sqlCommand = @" select * from vwFollowUp where cliente_idcliente = " + idcliente.ToString();
                }
                else
                {
                    sqlCommand = @" select * from vwFollowUp where cliente_idcliente = " + idcliente.ToString() + "and statusfollow_idstatus = " + status;
                }

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ViewFollowUp vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                    vf.DataStatus = reader.GetDateTime(18);
                    viewFollow.Add(vf);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return viewFollow;

        }




        public IList<ViewFollowUp> findPropostaPendente(int idfuncionario, bool gestor)
        {
            IList<ViewFollowUp> viewFollow = new List<ViewFollowUp>();
            try
            {
                conn.Open();
                String sqlCommand = @" select * 
                                        from vwFollowUp 
                                        where DATEDIFF(day,datastatus,getdate()) > 5 
                                        and statusfollow_idstatus not in (2,5,6) ";


                if (!gestor)
                {
                    sqlCommand += @" and funcionario_idfuncionario =" + idfuncionario.ToString();
                }
                else
                {
                    sqlCommand += @" and funcionario_idfuncionario in (SELECT funcionario_idfuncionario
                                                                        FROM [equipefuncionario]
                                                                        where equipe_idequipe = (SELECT equipe_idequipe
                                                                        FROM [equipefuncionario]
                                                                        where funcionario_idfuncionario = " + idfuncionario.ToString() + @"))";
                }
                
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ViewFollowUp vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                    vf.DataStatus = reader.GetDateTime(18);
                    viewFollow.Add(vf);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return viewFollow;

        }


        public IList<ViewFollowUp> findPropostaPendenteContato(int idcliente)
        {
            IList<ViewFollowUp> viewFollow = new List<ViewFollowUp>();
            try
            {
                conn.Open();
                String sqlCommand = @" select * 
                                        from vwFollowUp 
                                        where DATEDIFF(day,datastatus,getdate()) > 5 
                                        and statusfollow_idstatus not in (1,3,4,5,6) ";

                 sqlCommand += @" and cliente_idcliente = " + idcliente;

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ViewFollowUp vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                    vf.DataStatus = reader.GetDateTime(18);
                    viewFollow.Add(vf);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return viewFollow;

        }

        
        public ViewFollowUp findByIDProposta(string idProposta)
        {
            ViewFollowUp vf = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from vwFollowUp where idproposta=  " + idProposta;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return vf;

        }


        public IList<ViewFollowUp> findByStatusProposta(string idfuncionario, bool gestor, int idstatus)
        {
            IList<ViewFollowUp> viewFollow = new List<ViewFollowUp>();
            try
            {
                conn.Open();
                String sqlCommand = @" select * from vwFollowUp where statusfollow_idstatus =" + idstatus ;


                if (!gestor)
                {
                    sqlCommand += @" and  funcionario_idfuncionario =" + idfuncionario.ToString();
                }
                else
                {
                    sqlCommand += @" and funcionario_idfuncionario in (SELECT funcionario_idfuncionario
                                                                        FROM [equipefuncionario]
                                                                        where equipe_idequipe = (SELECT equipe_idequipe
                                                                        FROM [equipefuncionario]
                                                                        where funcionario_idfuncionario = " + idfuncionario.ToString() + @"))";
                }

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ViewFollowUp vf = new ViewFollowUp();
                    vf.IDFollowUp = reader.GetInt32(0);
                    vf.IDStatusFollow = reader.GetInt32(1);
                    vf.DescricaoStatus = reader.GetString(2);
                    vf.IDFuncionario = reader.GetInt32(3);
                    vf.NomeFuncionario = reader.GetString(4);
                    vf.CPFFuncionario = reader.GetString(5);
                    vf.IDProposta = reader.GetInt32(6);
                    vf.IDCLiente = reader.GetInt32(7);
                    vf.CNPJCliente = reader.GetString(8);
                    vf.NomeCliente = reader.GetString(9);
                    vf.CidadeCliente = reader.GetString(10);
                    vf.EstadoCliente = reader.GetString(11);
                    vf.DataProposta = reader.GetDateTime(12);
                    vf.ObservacoesFollow = reader.GetString(13);
                    vf.PrazoEntrega = reader.GetString(14);
                    vf.ValorTotal = reader.GetDecimal(15);
                    vf.Desconto = reader.GetDecimal(16);
                    vf.Quantidade = reader.GetInt32(17);
                    vf.DataStatus = reader.GetDateTime(18);
                    viewFollow.Add(vf);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return viewFollow;

        }


    }




}